package daoImpls;

import java.sql.*;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import dao.DAO;
import daoFactory.DaoFactory;
import dbc.ConnectionPool;
import vo.*;

public class UserDAOImpl<E> extends ObjectDAOImpl{

    public boolean add(User user) {
        User newUser =(User) user;
        Connection c = cp.getConnection();
        String sql = "insert into user(uName,Pwd,Usex) values(?,?,?)";
        int flag = 0;
        try (PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setString(1,newUser.getUname());
            ps.setString(2,newUser.getPwd());
            ps.setString(3,newUser.getSex());
          /*ps.setString(4,newUser.getEmail());
            ps.setString(5,newUser.getRelName());
            ps.setString(6,newUser.getAddress());
            ps.setString(7,newUser.getTelephone());
            ps.setDouble(8,newUser.getBalance());*/ 
            flag = ps.executeUpdate();
            ps.close();
            cp.returnConnection(c);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (flag != 0) return true;
        else return false;
    }

    @Override
    public void update(PetObject user) {
        User newUser =(User) user;
        System.out.println("updateUser:" + newUser);
        String sql = "update user set uname=?,pwd=?,usex=?,email=?,urelN=?,`add`=?,utel=?,ubalance=?,rid=? where uid = ?";
        try (Connection c = cp.getConnection();) {
        	PreparedStatement ps = c.prepareStatement(sql);
        	ps.setString(1, newUser.getUname());
        	ps.setString(2, newUser.getPwd());
        	ps.setString(3, newUser.getSex());
        	ps.setString(4, newUser.getEmail());
        	ps.setString(5, newUser.getRelName());
        	ps.setString(6, newUser.getAddress());
        	ps.setString(7, newUser.getTelephone());
        	ps.setDouble(8, newUser.getBalance());;
        	ps.setString(9, newUser.getRole().getRid());
        	ps.setString(10, newUser.getUid());
            ps.execute();
            cp.returnConnection(c);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void delete(String id) {
    	try (Connection c = cp.getConnection(); Statement s = c.createStatement();) {
            String sql = "delete from user where UID = '" + id+"'";
            System.out.println(sql);
            s.execute(sql);
            cp.returnConnection(c);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    public void deleteQuery(String ids) {
    	try (Connection c = cp.getConnection(); Statement s = c.createStatement();) {
            String sql = "delete from user where UID in (" + ids+")";
            System.out.println(sql);
            s.execute(sql);
            cp.returnConnection(c);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

	@Override
    public User get(String id) {
        User user = null;
        int num = Integer.parseInt(id);
        Connection c =cp.getConnection();
        try (Statement s = c.createStatement();) {
            String sql = "select * from user where uid = " + num;
            ResultSet rs = s.executeQuery(sql);
            if (rs.next()) {
                user = new User();
                user.setUid(rs.getString(1));
                user.setRole(DaoFactory.getRoleFactory().get(rs.getString(2)));
                user.setUname(rs.getString(3));
                user.setPwd(rs.getString(4));
                user.setSex(rs.getString(5));
                user.setEmail(rs.getString(6));
                user.setRelName(rs.getString(7));
                user.setAddress(rs.getString(8));
                user.setTelephone(rs.getString(9));
                user.setBalance(rs.getDouble(10));
            }
            rs.close();
            cp.returnConnection(c);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    public User getU(String username){
        User user = null;
        Connection c =cp.getConnection();
        try (Statement s = c.createStatement();) {
            ResultSet rs = s.executeQuery("select * from user where uName = '" + username + "'");
            if (rs.next()) {
                user = new User();
                user.setUid(rs.getString(1));
                user.setRole(DaoFactory.getRoleFactory().get(rs.getString(2)));
                user.setUname(rs.getString(3));
                user.setPwd(rs.getString(4));
                user.setSex(rs.getString(5));
                user.setEmail(rs.getString(6));
                user.setRelName(rs.getString(7));
                user.setAddress(rs.getString(8));
                user.setTelephone(rs.getString(9));
                user.setBalance(rs.getDouble(10));
            }
            rs.close();
            cp.returnConnection(c);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    @Override
    public List<PetObject> list(int start, int count) {
        List<PetObject> users = new ArrayList<>();
        String sql = "select * from user order by uid desc limit ?,? ";
        try (Connection c = cp.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
            ps.setInt(1, start);
            ps.setInt(2, count);
            ResultSet rs = ps.executeQuery();
           
            while (rs.next()) {
                User user = new User();
                user.setUid(rs.getString(1));
                user.setRole(DaoFactory.getRoleFactory().get(rs.getString(2)));
                user.setUname(rs.getString(3));
                user.setPwd(rs.getString(4));
                user.setSex(rs.getString(5));
                user.setEmail(rs.getString(6));
                user.setRelName(rs.getString(7));
                user.setAddress(rs.getString(8));
                user.setTelephone(rs.getString(9));
                user.setBalance(rs.getDouble(10));
                users.add(user);
            }
            rs.close();
            cp.returnConnection(c);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
    }
    public List<PetObject> list(Page page) {
        List<PetObject> users = new ArrayList<>();
        String sql = "select * from user order by uid desc limit ?,? ";
        try (Connection c = cp.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
            ps.setInt(1, page.getIndex()*page.getPageSize());
            ps.setInt(2, page.getPageSize());
            ResultSet rs = ps.executeQuery();
           
            while (rs.next()) {
                User user = new User();
                user.setUid(rs.getString(1));
                user.setRole(DaoFactory.getRoleFactory().get(rs.getString(2)));
                user.setUname(rs.getString(3));
                user.setPwd(rs.getString(4));
                user.setSex(rs.getString(5));
                user.setEmail(rs.getString(6));
                user.setRelName(rs.getString(7));
                user.setAddress(rs.getString(8));
                user.setTelephone(rs.getString(9));
                user.setBalance(rs.getDouble(10));
                users.add(user);
            }
            rs.close();
            cp.returnConnection(c);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
    }
	@Override
	public List<PetObject> list() {
		List<PetObject> users = new ArrayList<>();
        String sql = "select * from user order by uid desc";
        Connection c = cp.getConnection();
        try (PreparedStatement ps = c.prepareStatement(sql);) {
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setUid(rs.getString(1));
                user.setRole(DaoFactory.getRoleFactory().get(rs.getString(2)));
                user.setUname(rs.getString(3));
                user.setPwd(rs.getString(4));
                user.setSex(rs.getString(5));
                user.setEmail(rs.getString(6));
                user.setRelName(rs.getString(7));
                user.setAddress(rs.getString(8));
                user.setTelephone(rs.getString(9));
                user.setBalance(rs.getDouble(10));
                users.add(user);    
            }
            ps.close();
            cp.returnConnection(c);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
	}
	
	public List<PetObject> list(String s) {
		List<PetObject> users = new ArrayList<>();
        String sql = "select * from user where uname like '%"+ s +"%' order by uid desc";
        Connection c = cp.getConnection();
        try (PreparedStatement ps = c.prepareStatement(sql);) {
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setUid(rs.getString(1));
                user.setRole(DaoFactory.getRoleFactory().get(rs.getString(2)));
                user.setUname(rs.getString(3));
                user.setPwd(rs.getString(4));
                user.setSex(rs.getString(5));
                user.setEmail(rs.getString(6));
                user.setRelName(rs.getString(7));
                user.setAddress(rs.getString(8));
                user.setTelephone(rs.getString(9));
                user.setBalance(rs.getDouble(10));
                users.add(user);    
            }
            ps.close();
            cp.returnConnection(c);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
	}
    
}
